package edu.xmu.software.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import edu.xmu.software.pojo.UserInfo;

public class UserInfoDAO {
	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource dataSource){
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public UserInfo getUserInfoByUserName(String userName){
		Connection connection = null;
		try{
			final String AUTH_SQL = "select * from UserInfo where username = ?";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = connection.prepareStatement(AUTH_SQL);
			queryPs.setString(1, userName);
			ResultSet rs = queryPs.executeQuery();
			if (rs.next()){
				UserInfo userInfo = new UserInfo();
				userInfo.setUsername(rs.getString("username"));
				userInfo.setPassword(rs.getString("password"));
				userInfo.setEmail(rs.getString("email"));
				userInfo.setPhone(rs.getString("phone"));
				userInfo.setAddress(rs.getString("address"));
				userInfo.setName(rs.getString("name"));
				return userInfo;
			}
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public UserInfo delUserByName(String userName){
		Connection connection = null;
		try{
			final String QUERY_SQL = "select * from UserInfo where username = ?";
			final String DEL_SQL2 = "delete from userauth where username = ?";
			final String DEL_SQL3 = "delete from userinfo where username = ?";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = connection.prepareStatement(QUERY_SQL);
			PreparedStatement delPs2 = connection.prepareStatement(DEL_SQL2);
			PreparedStatement delPs3 = connection.prepareStatement(DEL_SQL3);
			queryPs.setString(1, userName);
			delPs2.setString(1, userName);
			delPs3.setString(1, userName);
			ResultSet rs = queryPs.executeQuery();
			try {
				delPs2.executeUpdate();
				delPs3.executeUpdate();
			} catch (Exception e) {
				System.err.println(e);
			}
			if (rs.next()){
				UserInfo userInfo = new UserInfo();
				userInfo.setUsername(rs.getString("username"));
				userInfo.setPassword(rs.getString("password"));
				userInfo.setEmail(rs.getString("email"));
				userInfo.setPhone(rs.getString("phone"));
				userInfo.setAddress(rs.getString("address"));
				userInfo.setName(rs.getString("name"));
				return userInfo;
			}
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}	
	public List<UserInfo> getAllUserInfo(){
		List<UserInfo> retValue = new ArrayList<UserInfo>();
		Connection connection = null;
		try{
			final String QUERY_SQL = "select * from UserInfo";
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement queryPs = connection.prepareStatement(QUERY_SQL);
			ResultSet rs = queryPs.executeQuery();
			while (rs.next()){
				UserInfo userInfo = new UserInfo();
				userInfo.setUsername(rs.getString("username"));
				userInfo.setPassword(rs.getString("password"));
				userInfo.setEmail(rs.getString("email"));
				userInfo.setPhone(rs.getString("phone"));
				userInfo.setAddress(rs.getString("address"));
				userInfo.setName(rs.getString("name"));
				retValue.add(userInfo);
			}
			return retValue;
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	public boolean isAuth (String userName, String password){
		Connection connection = null;
		try{
		final String AUTH_SQL = "select * from UserInfo where username = ? and password = ?";
		connection = jdbcTemplate.getDataSource().getConnection();
		PreparedStatement queryPs = connection.prepareStatement(AUTH_SQL);
		queryPs.setString(1, userName);
		queryPs.setString(2, password);
		ResultSet rs = queryPs.executeQuery();
		if (rs.next()){
			return true;
		}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	public boolean addUserInfo(UserInfo userInfo){
		final String INSERT_SQL =  "INSERT INTO UserInfo (username, password, name, email, phone,address) VALUES (?, ?, ?, ?, ?,?)";
		Connection connection = null;
		try{
			connection = jdbcTemplate.getDataSource().getConnection();
			PreparedStatement insertPs = connection.prepareStatement(INSERT_SQL);
			insertPs.setString(1, userInfo.getUsername());
			insertPs.setString(2, userInfo.getPassword());
			insertPs.setString(3, userInfo.getName());
			insertPs.setString(4, userInfo.getEmail());
			insertPs.setString(5, userInfo.getPhone());
			insertPs.setString(6, userInfo.getAddress());
			insertPs.execute();
			return true;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
}
